﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using DangKy.Objects;

namespace DangKy.Data
{
    public class DataAccess
    {
        public static SqlConnection KetNoiCSDL(string strChuoiKetNoi = @"Data Source=THANHTUAN-PC;Initial Catalog=CT30;Integrated Security=true;")
        {
            SqlConnection conn = new SqlConnection(strChuoiKetNoi);
            try
            {
                conn.Open();
            }
            catch (SqlException ex)
            {
                throw new Exception("Lỗi kết nối CSDL");
            }
            return conn;
        }

        public SinhVienOBJ LayThongTinSinhVien(string strMaSV)
        {
            SinhVienOBJ sv = new SinhVienOBJ();
            SqlConnection conn = KetNoiCSDL();
            string strTruyVan = "SELECT s.HO, s.TEN, s.NGAYSINH, l.TENLOP, s.NOISINH, n.MANGANH, n.TENNGANH FROM SINHVIEN s"
                + " INNER JOIN LOP l ON l.MALOP = s.MALOP"
                + " INNER JOIN NGANH n on n.MANGANH = l.MANGANH"
                + " WHERE s.MASV = @masv AND s.XOA = 0"; 
            SqlCommand com = new SqlCommand(strTruyVan, conn);
            com.Parameters.AddWithValue("@masv", strMaSV);
            SqlDataReader dr = com.ExecuteReader();
            if (dr.Read())
            {
                sv.MSSV = strMaSV;
                sv.HoTen = (string)dr["HO"] + " " + (string)dr["TEN"];
                sv.NgaySinh = (string)dr["NGAYSINH"];
                sv.Lop = (string)dr["TENLOP"];
                sv.DiaChi = (string)dr["NOISINH"];
                sv.MaNganh = (string)dr["MANGANH"];
                sv.TenNganh = (string)dr["TENNGANH"];
            }
            dr.Close();
            conn.Close();
            return sv;
        }

        public NganhOBJ LayChuyenNganh(string strMaNganh, string strTenNganh)
        {
            NganhOBJ ng = new NganhOBJ();
            ng.MaNganh = strMaNganh;
            ng.TenNganh = strTenNganh;

            SqlConnection conn = KetNoiCSDL();
            string strTruyVan = "SELECT MANGANH, TENNGANH FROM NGANH"
                + " WHERE MANGANHCHA = @manganh";
            SqlCommand com = new SqlCommand(strTruyVan, conn);
            com.Parameters.AddWithValue("@manganh", strMaNganh);
            SqlDataReader dr = com.ExecuteReader();
            while (dr.Read())
            {
                NganhOBJ n = new NganhOBJ();
                n.MaNganh = (string)dr["MANGANH"];
                n.TenNganh = (string)dr["TENNGANH"];
                ng.dsChuyenNganh.Add(n);
            }
            dr.Close();
            conn.Close();
            return ng;
        }

        public int DangKyChuyenNganh(string strMaSV, string strMaNganh)
        {
            int iRow = 0;
            SqlConnection conn = KetNoiCSDL();
            string strTruyVan = "INSERT INTO DKCHUYENNGANH VALUES(@masv, @manganh, (SELECT GETDATE()), 0);";
            SqlCommand com = new SqlCommand(strTruyVan, conn);
            com.Parameters.AddWithValue("@masv", strMaSV);
            com.Parameters.AddWithValue("@manganh", strMaNganh);
            iRow = com.ExecuteNonQuery();
            conn.Close();
            return iRow;
        }

        public int CapNhatDangKyChuyenNganh(string strMaSV, string strMaNganh)
        {
            int iRow = 0;
            SqlConnection conn = KetNoiCSDL();
            string strTruyVan = "UPDATE DKCHUYENNGANH SET MANGANH = @manganh, NGAYDK = (SELECT GETDATE()) WHERE MSSV = @masv;";
            SqlCommand com = new SqlCommand(strTruyVan, conn);
            com.Parameters.AddWithValue("@masv", strMaSV);
            com.Parameters.AddWithValue("@manganh", strMaNganh);
            iRow = com.ExecuteNonQuery();
            conn.Close();
            return iRow;
        }

        public string LayNganhDangKy(string strMaSV)
        {
            string strMaNganh = "";
            SqlConnection conn = KetNoiCSDL();
            string strTruyVan = "SELECT MANGANH FROM DKCHUYENNGANH"
                + " WHERE MSSV = @masv";
            SqlCommand com = new SqlCommand(strTruyVan, conn);
            com.Parameters.AddWithValue("@masv", strMaSV);
            SqlDataReader dr = com.ExecuteReader();
            if (dr.Read())
            {
                strMaNganh = (string)dr["MANGANH"];
            }
            dr.Close();
            conn.Close();
            return strMaNganh;
        }
    }
}
